using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class SQLHelper
    {
        //第一种方式
        static string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public static SqlConnection conn = new SqlConnection(str);
        //第二种方式
        //static SqlConnection conn
        //    = new SqlConnection(ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None).AppSettings.Settings["sqlConnection"].Value);


        // Scalar、NonQuery、DataSet、DataReader
        #region DataSet数据集访问实现
        public static DataSet GetQueryDataSet(string sql)
        {
            SqlDataAdapter ada = new SqlDataAdapter(sql, conn);//定义DataAdapter对象，完成数据库操作及连接
            DataSet ds = new DataSet();//定义一个数据集

            ada.Fill(ds);//使用DataAdapter结果集填充DataSet数据集
            return ds;
        }

        public static DataSet GetDataSet(SqlCommand cmd)
        {
            //如果cmd是没有内容，返回null值
            if (string.IsNullOrEmpty(cmd.CommandText))
                return null;
            //判断数据库连接是否打开
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();//定义一个数据集
            try
            {
                ada.Fill(ds);//使用DataAdapter结果集填充DataSet数据集
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        #endregion

        /// <summary>
        /// ExcuteReader读库
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExcuteReader(SqlCommand cmd)
        {
            conn.Close();
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            SqlDataReader read;
            try
            {
                read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return read;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 对库执行CRUD操作
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>影响行数：num</returns>
        public static int ExcuteSQL(SqlCommand cmd)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            int num = 0;//定义整形对象，用于存储ExcuteNonQuery操作影响的行数
            try
            {
                num = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //关闭数据库李连杰
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return num;
        }
        /// <summary>
        /// 使用ExecuteScalar查找DB，返回第一行、第一列数据
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>object</returns>
        public static object GetScalar(SqlCommand cmd)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            object obj = new SqlCommand();
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //关闭数据库李连杰
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return obj;
        }

        public static MemoryStream GetPhotoByID(SqlCommand cmd)
        {
            cmd.Connection = conn;//进行DB连接
            try
            {
                conn.Close();
                //如果DB是关闭状态，进行DB的打开
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                MemoryStream streamDate = new MemoryStream();
                Byte[] byteImage = new Byte[0];

                var obj = cmd.ExecuteScalar();//读库

                if (obj != null)
                {
                    byteImage = (Byte[])obj;//强制转型
                    streamDate = new MemoryStream(byteImage);
                    return streamDate;
                }
                else
                    return null;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
